"""
Case Type   : toast表
Case Name   : 设置toast.autocacuum_enable为false，手动控制toast表的自动清理
Create At   : 2024/08/07
Owner       : songjing20
Description :
    1.关闭自动VACUUM操作对toast表的处理
    2.建表指定toast.autocacuum_enable=false，查询字段信息
    3.插入100条数据
    4.查看toast表行数
    5.查看原表和toast表文件大小
    6.删除部分数据
    7.查看toast表行数
    8.查看原表和toast表文件大小
    9.手动清理toast表，vacuum full
    10.查看原表和toast表文件大小
    11.清理环境
Expect      :
    1.成功
    2.成功，id存储策略plain，info存储策略为extended
    3.成功
    4.行数不为0
    5.原表大于toast表
    6.成功
    7.与步骤4相同
    8.与步骤5相同
    9.成功
    10.小于步骤5结果
    11.成功
History     :
"""

import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class DmlTaost(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.toast_para = 'handle_toast_in_autovac'
        self.default_val = self.common.show_param(self.toast_para)
        self.tb_name = 't_toast_0001'

    def test_toast_table(self):
        text = '-----step1:关闭自动VACUUM操作对toast表的处理，' \
               '设置autovacuum_naptime=10s;expect:成功-----'
        self.log.info(text)
        self.flag = 0
        if 'off' != self.default_val.lower():
            res = self.pri_sh.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                f"{self.toast_para} = off")
            self.assertTrue(res, f'执行失败:{text}')
            self.flag = 1

        text = '-----step2:建表指定toast.autocacuum_enable=false，查询字段信息;' \
               'expect:成功，id存储策略plain，info存储策略为extended-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.tb_name} cascade;"
            f"create table {self.tb_name}("
            f"id serial primary key, info text not null"
            f") with(toast.autovacuum_enabled = false); \\d+ {self.tb_name}")
        self.log.info(res)
        self.assertEqual(res.count(self.constant.CREATE_TABLE_SUCCESS), 3,
                         f'建表失败:{text}')
        self.assertIn(f"id     | integer | not null default "
                      f"nextval('{self.tb_name}_id_seq'::regclass) | plain",
                      res, f'执行失败:{text}')
        self.assertIn(f"info   | text    | not null                           "
                      f"                       | extended", res, f'执行失败:{text}')

        text = '-----step3:插入100条数据;expect:成功-----'
        self.log.info(text)
        insert_res = self.pri_sh.execut_db_sql(
            f"insert into {self.tb_name}(info) values("
            f"repeat('abc',100000) || generate_series(1,100));")
        self.log.info(insert_res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      f'执行失败:{text}')

        text = '-----step4:查看toast表行数;expect:行数不为0-----'
        self.log.info(text)
        toast_tb = self.pri_sh.execut_db_sql(
            f"select relname from pg_class "
            f"where relfilenode = (select reltoastrelid from pg_class "
            f"where relname = '{self.tb_name}');").splitlines()[-2].strip()
        self.log.info(toast_tb)
        sql_count = f"select count(*) from pg_toast.{toast_tb};"
        count_res = self.pri_sh.execut_db_sql(sql_count)
        self.log.info(count_res)
        self.assertIn('200', count_res, f'执行失败:{text}')

        text = '-----step5:查看原表和toast表文件大小;expect:成功-----'
        self.log.info(text)
        sql_size = f"select pg_size_pretty(pg_total_relation_size(" \
                   f"'{self.tb_name}')) as tb_size," \
                   f"pg_size_pretty(pg_total_relation_size(" \
                   f"'pg_toast.{toast_tb}')) as toast_size;"
        size_res = self.pri_sh.execut_db_sql(sql_size)
        self.log.info(size_res)
        res5 = size_res.splitlines()[-2].split()
        self.assertTrue(int(res5[0]) > int(res5[3]) > 0, f'执行失败:{text}')

        text = '-----step6:删除部分数据;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f"delete from {self.tb_name} where id %2=0;")
        self.log.info(res)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, res, f'执行失败:{text}')

        text = '-----step7:查看toast表行数;expect:100-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(sql_count)
        self.log.info(res)
        self.assertIn(' 100\n(1 row)', res, f'执行失败:{text}')

        text = '-----step8:查看原表和toast表文件大小;expect:与步骤5相同-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(sql_size)
        self.log.info(res)
        self.assertEqual(res, size_res, f'执行失败:{text}')

        text = '-----step9:手动清理toast表，vacuum full;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(f"vacuum full {self.tb_name};")
        self.log.info(res)
        self.assertIn(self.constant.VACUUM_SUCCESS_MSG, res, f'执行失败:{text}')

        text = '-----step10:查看原表和toast表文件大小;expect:小于步骤5结果-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(sql_size)
        self.log.info(res)
        res10 = res.splitlines()[-2].split()
        self.assertLess(res10, res5, f'执行失败:{text}')

    def tearDown(self):
        text = '-----step11:清理环境;expect:成功-----'
        self.log.info(text)
        drop_res = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.tb_name} cascade;")
        self.log.info(drop_res)
        if self.flag:
            res = self.pri_sh.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                f"{self.toast_para} = {self.default_val}")
            self.assertTrue(res, f'执行失败:{text}')
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_res,
                      f'删表失败:{text}')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
